package service.impl;

import DAO.BaseDAO;
import entity.text.ChapterEntity;
import entity.text.PaperEntity;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.dom4j.Attribute;
import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;
import org.dom4j.tree.DefaultAttribute;
import org.dom4j.tree.DefaultElement;
import service.BuildingNetworkService;
import util.CollectionUtils;
import util.JDBCUtil;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;

/**
 * @author ：hzs
 * @date ：Created in 2021/3/27 17:14
 * @description：
 * @modified By：
 * @version: $
 */
public class BuildingMedicalNetworkServiceImpl implements BuildingNetworkService {

    private BaseDAO baseDAO = new BaseDAO();
    private static String folderPath = "D:\\paper_database\\Lancet0324";
    private static int K = 100;

    @Override
    public List<PaperEntity> readPapersFromFiles() {
        File folder = new File(folderPath);
        SAXReader saxReader = new SAXReader();
        String header = "INSERT INTO paper (url, title, AUTHORS, xmlfile, publish_date) VALUE ";
        StringBuilder sb = new StringBuilder();
        String format = "('%s', '%s', '%s', '%s', '%s')";
        boolean needComma = false;
        if (folder.exists()) {
            Connection connection = JDBCUtil.getConnection();
            File[] listFiles = folder.listFiles();
            for (int i = 0; i < listFiles.length; i++) {
                File file = listFiles[i];
                Document document = null;
                try {
                    document = saxReader.read(file);
                } catch (DocumentException e) {
                    e.printStackTrace();
                }
                Element rootElement = document.getRootElement();
                String fileName = file.getName();
                String url = rootElement.element("url").getText();
                String publicationDate = rootElement.element("publicationDate").getText();
                String title = rootElement.element("title").getText().replace("\'", "\\\'");
                String authors = rootElement.element("authors").getText().replace("\'", "\\\'");
                if (needComma) {
                    sb.append("," + String.format(format, url, title, authors, fileName, publicationDate));
                } else {
                    sb.append(String.format(format, url, title, authors, fileName, publicationDate));
                    needComma = true;
                }

                if (i % K == K-1) {
                    String sql = header.concat(sb.toString());
                    sb.setLength(0);
                    needComma = false;
                    try {
                        Statement statement = connection.createStatement();
                        boolean execute = statement.execute(sql);
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            }

            String sql = header.concat(sb.toString());
            try {
                Statement statement = connection.createStatement();
                boolean execute = statement.execute(sql);
            } catch (SQLException e) {
                e.printStackTrace();
            }

            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    public List<Reference> getReferencesFromFile() {

        List<List<Object>> list = baseDAO.executeReadSql("SELECT id, title FROM paper");

        List<Reference> result = new ArrayList<>();
        File folder = new File(folderPath);
        SAXReader saxReader = new SAXReader();
        if (folder.exists()) {
            for (File file : folder.listFiles()) {
                String sourceId = "";
                try {
                    Document document = saxReader.read(file);
                    List<DefaultElement> references = document.getRootElement().element("references").elements();
                    if (!CollectionUtils.isEmpty(references)) {
                        for (DefaultElement defaultElement : references) {
                            String label = defaultElement.attribute("label").getValue();
                            Element element = defaultElement.element("authors");
                            String authors = element == null ? "" : element.getText();
                            element = defaultElement.element("title");
                            String title = element == null ? "" : element.getText();
                            String host = defaultElement.element("host").getText();

                            if (host.contains("Lancet")) {
                                for (List<Object> objects : list) {
                                    String id = objects.get(0).toString();
                                    String title2 = (String) objects.get(1);
                                    if (getSimilarity(title, title2) > 0.8) {
                                        if ("".equals(sourceId)) {
                                            List<List<Object>> lists = baseDAO.executeReadSql("SELECT id FROM paper WHERE xmlfile = '" +
                                                    file.getName() + "'");
                                            if (CollectionUtils.isEmpty(list)) {
                                                continue;
                                            }
                                            sourceId = lists.get(0).get(0).toString();
                                        }
                                        Reference reference = new Reference();
                                        reference.fileName = file.getName();
                                        reference.label = label;
                                        reference.sourceId = sourceId;
                                        reference.targetId = id;
                                        result.add(reference);
                                    }
                                }

                            }
                        }
                    }
                } catch (DocumentException e) {
                    e.printStackTrace();
                }
            }
        }
        return result;
    }

    public void insertReferences(List<Reference> references) {
        String header = "INSERT INTO referencestable (paperid, referedid, fileid, label) VALUE ";
        StringBuilder sb = new StringBuilder();
        String format = "('%s', '%s', '%s', '%s')";
        Connection connection = JDBCUtil.getConnection();
        boolean needComma = false;
        for (int i = 0; i < references.size(); i++) {
            String paperid = references.get(i).sourceId;
            String referedid = references.get(i).targetId;
            String fileid = references.get(i).fileName;
            String label = references.get(i).label;

            if (needComma) {
                sb.append("," + String.format(format, paperid, referedid, fileid, label));
            } else {
                sb.append(String.format(format, paperid, referedid, fileid, label));
                needComma = true;
            }
            if (i % K == K-1) {
                String sql = header.concat(sb.toString());
                sb.setLength(0);
                needComma = false;
                try {
                    Statement statement = connection.createStatement();
                    boolean execute = statement.execute(sql);
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

        String sql = header.concat(sb.toString());
        try {
            Statement statement = connection.createStatement();
            boolean execute = statement.execute(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        try {
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public double getSimilarity(String a, String b) {
        String reg = "[^0-9a-zA-Z ]";
        a = a.replaceAll(reg,"");
        b = b.replaceAll(reg,"");
        String[] aSplitArray = a.split(" ");
        String[] bSplitArray = b.split(" ");
        int count = Math.min(aSplitArray.length, bSplitArray.length);
        if (count == 0) {
            return 0;
        }

        float mergeNum = 0;//并集元素个数
        float commonNum = 0;//相同元素个数（交集）

        for(String s1:aSplitArray) {
            for(String s2:bSplitArray) {
                if(s1.equalsIgnoreCase(s2)) {
                    commonNum++;
                    break;
                }
            }
        }

        mergeNum = aSplitArray.length + bSplitArray.length - commonNum;

        return commonNum/mergeNum;
    }

    public void insertCommunityId() {
        String header = "UPDATE paper SET communityid = CASE id ";
        StringBuilder sb = new StringBuilder();
        String format = " when %s then %s ";
        Connection connection = JDBCUtil.getConnection();

        File file = new File("D://gnresult.xlsx");
        DataFormatter dataFormatter = new DataFormatter();
        XSSFWorkbook workbook = null;
        try {
            workbook = new XSSFWorkbook(file);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        }
        Sheet sheet = workbook.getSheetAt(0);
        int lastRowNum = sheet.getLastRowNum();
        for (int i = 0; i < lastRowNum; i++) {
            Row row = sheet.getRow(i);
            String paperId = dataFormatter.formatCellValue(row.getCell(0));
            String community = dataFormatter.formatCellValue(row.getCell(1));
            sb.append(String.format(format, paperId, community));

        }

        String sql = header.concat(sb.toString()).concat(" end");
        try {
            Statement statement = connection.createStatement();
            boolean execute = statement.execute(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        try {
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    public static void main(String[] args) {
        BuildingMedicalNetworkServiceImpl buildingMedicalNetworkService = new BuildingMedicalNetworkServiceImpl();
//        List<PaperEntity> paperEntities = buildingMedicalNetworkService.readPapersFromFiles();
//        List<Reference> referencesFromFile = buildingMedicalNetworkService.getReferencesFromFile();
//        buildingMedicalNetworkService.insertReferences(referencesFromFile);
        buildingMedicalNetworkService.insertCommunityId();
    }

    static class Paper {
        String url;
        String publicationDate;
        String title;
        String authors;
    }

    static class Reference {
        String sourceId;
        String targetId;
        String label;
        String fileName;
    }

}


